package cn.edu.lingnan.dao;


import cn.edu.lingnan.pojo.CourseDTO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CourseDaoImp implements CourseDao {
    @Override
    public List<CourseDTO> pageQuery(Integer page, Integer pageSize) {
            List<CourseDTO> courseList = new ArrayList<>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            String sql = "SELECT c.cid, c.tid, c.cname, t.tname,c.ctime, c.credit " +
                    "FROM school.course c JOIN school.teacher t ON c.tid = t.tid LIMIT ?, ?";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, (page - 1) * pageSize);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                CourseDTO course = new CourseDTO();
                course.setCid(rs.getInt("cid"));
                course.setTid(rs.getInt("tid"));
                course.setCname(rs.getString("cname"));
                course.setCtime(rs.getString("ctime"));
                course.setTname(rs.getString("tname"));
                course.setCredit(rs.getInt("credit"));
                courseList.add(course);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return courseList;

    }

    @Override
    public Integer  getTotalCourses() {
            Integer totalCourses = 0;
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*) FROM school.course");

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                totalCourses = rs.getInt(1);
            }

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return totalCourses;
    }

    @Override
    public List<CourseDTO> getCoursesByName(String courseName) {
        List<CourseDTO> courseList = new ArrayList<>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话

            PreparedStatement ps = conn.prepareStatement("SELECT * FROM school.course WHERE cname LIKE ?");

            ps.setString(1, "%" + courseName + "%");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                CourseDTO course = new CourseDTO();
                course.setCid(rs.getInt("cid"));
                course.setTid(rs.getInt("tid"));
                course.setCname(rs.getString("cname"));
                course.setCtime(rs.getString("ctime"));
                course.setTname(rs.getString("cname"));
                course.setCredit(rs.getInt("credit"));
                courseList.add(course);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return courseList;
    }




    public static void main(String[] args) {
        CourseDaoImp courseDaosImp = new CourseDaoImp();
        List<CourseDTO> courseList = courseDaosImp.pageQuery(1, 10);
        for (CourseDTO course : courseList) {
            System.out.println(course);
        }
    }
}
